import os
import time
import tarfile
import schedule
import datetime
import smtplib
import pymysql
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email.mime.text import MIMEText
from email.utils import COMMASPACE
from email import encoders
#from mysql.connector import connect, Error
from pytz import timezone

# MySQL连接信息
MYSQL_CONFIG = {
    "host": "124.221.11.239",
    "user": "root",
    "password": "aDxcFHfF3I4GS2",
    "database": "php_exp_store",
}

# 邮箱发送配置
EMAIL_CONFIG = {
    "smtp_server": "smtp.163.com",
    "port": 25,
    "sender_email": "thkscctv@163.com",
    "sender_password": "QZKBBMUKUMVKQIHM",
    "recipient_email": "indexman@126.com",
}

# 备份文件路径及名称
BACKUP_DIR = "D:/tmp/backup/db"
BACKUP_FILENAME = f"{datetime.datetime.now(timezone('Asia/Shanghai')).strftime('%Y%m%d%H%M%S')}.sql"
BACKUP_PATH = os.path.join(BACKUP_DIR, BACKUP_FILENAME)
TAR_GZ_BACKUP_PATH = f"{BACKUP_PATH}.tar.gz"

def backup_mysql():
    try:
        # cnx = connect(**MYSQL_CONFIG)
        # connect
        cnx =  pymysql.connect(**MYSQL_CONFIG)
        # cursor = cnx.cursor()
        backup_cmd = f"mysqldump -h{MYSQL_CONFIG['host']} -u{MYSQL_CONFIG['user']} -p{MYSQL_CONFIG['password']} {MYSQL_CONFIG['database']} > {BACKUP_PATH}"
        os.system(backup_cmd)
        # cursor.close()
        cnx.close()
    except Exception as e:
        print(f"Error occurred during MySQL backup: {e}")
        return False
    return True

def compress_backup():
    with tarfile.open(TAR_GZ_BACKUP_PATH, "w:gz") as tar:
        tar.add(BACKUP_PATH, arcname=BACKUP_FILENAME)
    os.remove(BACKUP_PATH)

def send_email(attachment_path):
    msg = MIMEMultipart()
    msg['From'] = EMAIL_CONFIG['sender_email']
    msg['To'] = COMMASPACE.join([EMAIL_CONFIG['recipient_email']])
    msg['Subject'] = f"MySQL Backup ({datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')})"

    with open(attachment_path, 'rb') as f:
        attachment = MIMEBase('application', 'octet-stream')
        attachment.set_payload(f.read())
        encoders.encode_base64(attachment)
        attachment.add_header('Content-Disposition', f'attachment; filename="{os.path.basename(attachment_path)}"')
        msg.attach(attachment)

    server = smtplib.SMTP(EMAIL_CONFIG['smtp_server'], EMAIL_CONFIG['port'])
    server.starttls()
    server.login(EMAIL_CONFIG['sender_email'], EMAIL_CONFIG['sender_password'])
    text = msg.as_string()
    server.sendmail(EMAIL_CONFIG['sender_email'], [EMAIL_CONFIG['recipient_email']], text)
    server.quit()

def run_backup_job():
    if backup_mysql():
        compress_backup()
        send_email(TAR_GZ_BACKUP_PATH)

# 定时任务配置，每天凌晨1点执行
# schedule.every().day.at("01:00").do(run_backup_job)

#while True:
#    schedule.run_pending()
#    time.sleep(1)

# 如果不希望使用定时任务循环，可以注释掉上面的while循环，并直接调用run_backup_job()函数一次
run_backup_job()
